package org.vy.dal.dao;

/**
 * @author Ravi Shankar
 *
 */

import java.util.List;

import org.vy.dal.QueryManager;

public class VyCitiesDAO {  		
	private static final String FIND_BY_PK = "SELECT * FROM <TABLE> WHERE CITY_CODE = m_CityCode";
	private static final String FIND_BY_PARAMETER = "SELECT * FROM <TABLE> WHERE 1 = 1 ";
	private static final String UPDATE = "UPDATE <TABLE> SET CITY_CODE=m_CityCode,CITY_NAME=m_CityName,STATE_CODE=m_Statecode,STATE_NAME=m_StateName,COUNTRY_CODE=m_Countrycode,COUNTRY_NAME=m_CountryName WHERE CITY_CODE = m_CityCode";
	private static final String INSERT = "INSERT INTO <TABLE> (CITY_CODE,VY_ID,CITY_NAME,STATE_CODE,STATE_NAME,COUNTRY_CODE, COUNTRY_NAME) VALUES(m_CityCode,m_CityName,m_Statecode,m_StateName,m_Countrycode,m_CountryName)";
	private static final String DELETE = "DELETE FROM <TABLE> WHERE CITY_CODE = m_CityCode";

	public static VyCitiesDO findByPK(String key) throws Exception {
		VyCitiesDO dto = new VyCitiesDO();
		dto.setCityCode(key);
		dto = (VyCitiesDO) QueryManager.executeQuery(dto, FIND_BY_PK);
		return dto;
	}

	public static VyCitiesDO insert(VyCitiesDO dto) throws Exception {

		dto = (VyCitiesDO) QueryManager.executeInsert(dto, INSERT);
		return dto;
	}

	public static VyCitiesDO delete(String key) throws Exception {
		VyCitiesDO dto = new VyCitiesDO();
		dto.setCityCode(key);
		boolean i = QueryManager.executeDelete(dto, DELETE);
		if (i == true)
			return dto;
		else
			return null;
	}

	public static VyCitiesDO update(VyCitiesDO dto) throws Exception {
		int i = QueryManager.executeUpdate(dto, UPDATE);
		if (i > 0)
			return dto;
		else
			return null;
	}

	@SuppressWarnings("unchecked")
	public static List<VyCitiesDO> findByParam(VyCitiesDO dto) throws Exception {

		StringBuilder sb = new StringBuilder(FIND_BY_PARAMETER);
		if (dto.getCityCode() != null) {
			sb.append(" AND CITY_CODE LIKE '%" + dto.getCityCode() + "%' ");
		}
		if (dto.getCityName() != null) {
			sb.append(" AND CITY_NAME LIKE '%" + dto.getCityName() + "%' ");
		}
		if (dto.getStatecode() != null) {
			sb.append(" AND STATE_CODE LIKE '%" + dto.getStatecode() + "%' ");
		}
		if (dto.getStateName() != null) {
			sb.append(" AND STATE_NAME LIKE '%" + dto.getStateName() + "%' ");
		}
		if (dto.getCountrycode() != null) {
			sb.append(" AND COUNTRY_CODE LIKE '%" + dto.getCountrycode() + "%' ");
		}
		if (dto.getCountryName() != null) {
			sb.append(" AND COUNTRY_NAME LIKE '%" + dto.getCountryName() + "%' ");
		}

		System.out.println(sb.toString());
		return (List<VyCitiesDO>) QueryManager.executeListQuery(dto, sb
				.toString());
	}
}
